# -*- coding: utf-8 -*-
'''
| @author: stone.li
| Created on: 2022/12/1 14:01
| desc: 企业分层页面SQL查询
'''

import logging,json
from proj_spec.IDS.comparison_industries import chain_id_transformation
from util.db_util import execute_query


def get_company_classify_num_time(region_code, tree_id, num_time):
    """查询地区+地区下级地区的分类下的企业数量
    :param region_code：地区code
    :param tree_id:产业链ID
    :param num_time:1-企业数量/2-注册时间
    :return: 统计的企业数量
    """
    sql = "SELECT content FROM industry_cockpit.t_com_layer_calc WHERE tree_id = %s AND region_code = '%s' AND type = %s;" % (tree_id, region_code, num_time)
    try:
        res = execute_query(sql, dbname="DB_cockpit")
        return json.loads(res[0][0])
    except Exception as e:
        logging.error("get_company_classify_num_time--SQL查询失败：%s" % e)


def get_company_register_capital(region_code, tree_id):
    """查询地区公司注册资本对比
    :param region_code：地区code
    :param tree_id:产业链ID
    :return: 公司注册资本
    """
    chain_his_id = chain_id_transformation(tree_id)
    sql_region_code = "select code from region_dictionary where parent_code = '%s' " % region_code
    codes = execute_query(sql_region_code)
    # 有地级市没有区县的情况,不再执行第二个SQL，直接返回
    if not codes:
        return codes
    region_codes = [i[0] for i in codes]
    sql_capital = '''SELECT ent_0_1_million,ent_1_5_million,ent_5_10_million,ent_10_50_million,ent_50_100_million,ent_100_million_more FROM datascope.ent_tree_info_past_year_stats  WHERE chain_his_id = %s AND region_code in {}'''.format(tuple(region_codes)) % chain_his_id
    try:
        res = execute_query(sql_capital)
    except Exception as e:
        logging.error("get_company_register_capital--SQL查询失败：%s" % e)
    # 新疆/海南个别地区无数据，sql查询会为空，少一条地区数据
    k = abs(len(res) - len(region_codes))
    if k != 0:
        for i in range(k):
            res.append((0, 0, 0, 0, 0, 0))
    Result = []
    for j in range(len(res[0])):
        reg = [i[j] for i in res]
        Result.append(reg)
    return Result


def get_company_qualification(region_code, tree_id):
    """查询地区各个资质下企业数量
    :param region_code：地区code
    :param tree_id:产业链ID
    :return: 企业资质数量
    """
    chain_his_id = chain_id_transformation(tree_id)
    sql = "SELECT gov_reward_ent_other_count, tech_demon_ent_other_count, std_maker_ent_other_count, innov_award_ent_other_count, " \
             "adv_tech_ent_other_count, high_tech_ent_other_count, tech_center_ent_other_count, tech_sme_ent_other_count, tech_special_ent_other_count " \
             "FROM datascope.ent_tree_info_past_year_stats WHERE chain_his_id = %s AND region_code = '%s' " % (chain_his_id, region_code)
    try:
        res = execute_query(sql)
        return res[0]
    except Exception as e:
        logging.error("get_company_qualification--SQL查询失败：%s" % e)
